package com.rickpan.repository;

import com.rickpan.entity.FileInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 文件信息数据访问层
 */
@Repository
public interface FileInfoRepository extends JpaRepository<FileInfo, Long> {

    /**
     * 根据所有者和父目录查找文件列表
     */
    List<FileInfo> findByOwnerIdAndParentIdAndIsDeletedFalseOrderByIsDirectoryDescCreatedAtDesc(
            Long ownerId, Long parentId);

    /**
     * 根据所有者查找根目录文件列表
     */
    List<FileInfo> findByOwnerIdAndParentIdIsNullAndIsDeletedFalseOrderByIsDirectoryDescCreatedAtDesc(
            Long ownerId);

    /**
     * 根据文件哈希查找文件
     */
    Optional<FileInfo> findByFileHashAndIsDeletedFalse(String fileHash);

    /**
     * 根据文件SHA256查找文件（用于去重）
     */
    Optional<FileInfo> findByFileSha256AndIsDeletedFalse(String fileSha256);

    /**
     * 根据用户ID和文件SHA256查找文件
     */
    Optional<FileInfo> findByOwnerIdAndFileSha256AndIsDeletedFalse(Long ownerId, String fileSha256);

    /**
     * 根据用户ID和文件SHA256查找所有文件 - 处理重复记录
     */
    List<FileInfo> findAllByOwnerIdAndFileSha256AndIsDeletedFalse(Long ownerId, String fileSha256);

    /**
     * 统计用户文件数量（按类型和删除状态）
     */
    Long countByOwnerIdAndIsDirectoryAndIsDeleted(Long ownerId, Boolean isDirectory, Boolean isDeleted);

    /**
     * 获取用户最近文件（按更新时间排序）
     */
    List<FileInfo> findByOwnerIdAndIsDirectoryAndIsDeletedOrderByUpdatedAtDesc(
            Long ownerId, Boolean isDirectory, Boolean isDeleted, Pageable pageable);

    /**
     * 获取用户所有文件（按类型和删除状态）
     */
    List<FileInfo> findByOwnerIdAndIsDirectoryAndIsDeleted(Long ownerId, Boolean isDirectory, Boolean isDeleted);

    /**
     * 根据用户ID、父目录ID和文件SHA256查找文件 - 精确去重
     */
    List<FileInfo> findByOwnerIdAndParentIdAndFileSha256AndIsDeletedFalse(Long ownerId, Long parentId, String fileSha256);

    /**
     * 根据用户ID和文件SHA256查找根目录文件 - 精确去重
     */
    List<FileInfo> findByOwnerIdAndParentIdIsNullAndFileSha256AndIsDeletedFalse(Long ownerId, String fileSha256);

    /**
     * 根据用户ID、父目录ID和原始文件名查找文件 - 文件名重复检查
     */
    List<FileInfo> findByOwnerIdAndParentIdAndOriginalNameAndIsDeletedFalse(Long ownerId, Long parentId, String originalName);

    /**
     * 根据用户ID和原始文件名查找根目录文件 - 文件名重复检查
     */
    List<FileInfo> findByOwnerIdAndParentIdIsNullAndOriginalNameAndIsDeletedFalse(Long ownerId, String originalName);

    /**
     * 根据ID和用户ID查找文件
     */
    Optional<FileInfo> findByIdAndOwnerIdAndIsDeletedFalse(Long id, Long ownerId);

    /**
     * 根据ID查找未删除的文件
     */
    Optional<FileInfo> findByIdAndIsDeletedFalse(Long id);

    /**
     * 根据用户ID和父目录ID查找文件（分页）
     */
    Page<FileInfo> findByOwnerIdAndParentIdAndIsDeletedFalse(Long ownerId, Long parentId, Pageable pageable);

    /**
     * 根据用户ID查找根目录文件（parentId为null）
     */
    Page<FileInfo> findByOwnerIdAndParentIdIsNullAndIsDeletedFalse(Long ownerId, Pageable pageable);

    /**
     * 根据用户ID、父目录ID和文件名搜索（分页）
     */
    Page<FileInfo> findByOwnerIdAndParentIdAndOriginalNameContainingAndIsDeletedFalse(
            Long ownerId, Long parentId, String originalName, Pageable pageable);

    /**
     * 根据用户ID和文件名搜索根目录文件（parentId为null）
     */
    Page<FileInfo> findByOwnerIdAndParentIdIsNullAndOriginalNameContainingAndIsDeletedFalse(
            Long ownerId, String originalName, Pageable pageable);

    /**
     * 根据用户ID和文件名全局搜索（用于搜索建议）
     */
    List<FileInfo> findByOwnerIdAndOriginalNameContainingIgnoreCaseAndIsDeletedFalseOrderByUpdatedAtDesc(
            Long ownerId, String originalName, Pageable pageable);

    /**
     * 检查文件名是否存在
     */
    boolean existsByOwnerIdAndParentIdAndOriginalNameAndIsDeletedFalse(
            Long ownerId, Long parentId, String originalName);

    /**
     * 根据用户ID和父目录ID查找所有文件
     */
    List<FileInfo> findByOwnerIdAndParentIdAndIsDeletedFalse(Long ownerId, Long parentId);

    /**
     * 根据所有者和文件名查找文件
     */
    Optional<FileInfo> findByOwnerIdAndOriginalNameAndParentIdAndIsDeletedFalse(
            Long ownerId, String originalName, Long parentId);

    /**
     * 根据所有者查找最近修改的文件
     */
    @Query("SELECT f FROM FileInfo f WHERE f.ownerId = :ownerId AND f.isDeleted = false AND f.isDirectory = false ORDER BY f.updatedAt DESC")
    Page<FileInfo> findRecentFilesByOwnerId(@Param("ownerId") Long ownerId, Pageable pageable);

    /**
     * 根据所有者统计文件数量
     */
    @Query("SELECT COUNT(f) FROM FileInfo f WHERE f.ownerId = :ownerId AND f.isDeleted = false AND f.isDirectory = false")
    long countFilesByOwnerId(@Param("ownerId") Long ownerId);

    /**
     * 根据所有者统计文件夹数量
     */
    @Query("SELECT COUNT(f) FROM FileInfo f WHERE f.ownerId = :ownerId AND f.isDeleted = false AND f.isDirectory = true")
    long countFoldersByOwnerId(@Param("ownerId") Long ownerId);

    /**
     * 根据所有者统计存储使用量
     */
    @Query("SELECT SUM(f.fileSize) FROM FileInfo f WHERE f.ownerId = :ownerId AND f.isDeleted = false AND f.isDirectory = false")
    Long getStorageUsedByOwnerId(@Param("ownerId") Long ownerId);

    /**
     * 计算用户实际存储使用量（只统计未删除的文件）
     * 这是 getStorageUsedByOwnerId 的别名方法，用于存储修复服务
     */
    default Long calculateUserStorageUsage(Long userId) {
        return getStorageUsedByOwnerId(userId);
    }

    /**
     * 搜索文件
     */
    @Query("SELECT f FROM FileInfo f WHERE f.ownerId = :ownerId AND f.isDeleted = false AND " +
           "(f.originalName LIKE %:keyword% OR f.fileName LIKE %:keyword%) " +
           "ORDER BY f.isDirectory DESC, f.updatedAt DESC")
    Page<FileInfo> searchFiles(@Param("ownerId") Long ownerId, @Param("keyword") String keyword, Pageable pageable);

    /**
     * 查找回收站文件
     */
    @Query("SELECT f FROM FileInfo f WHERE f.ownerId = :ownerId AND f.isDeleted = true ORDER BY f.updatedAt DESC")
    Page<FileInfo> findDeletedFilesByOwnerId(@Param("ownerId") Long ownerId, Pageable pageable);

    /**
     * 根据路径查找文件
     */
    Optional<FileInfo> findByFilePathAndIsDeletedFalse(String filePath);

    /**
     * 检查文件名是否在指定目录下存在
     */
    boolean existsByOwnerIdAndOriginalNameAndParentIdAndIsDeletedFalse(
            Long ownerId, String originalName, Long parentId);

    /**
     * 查找子文件和文件夹
     */
    @Query("SELECT f FROM FileInfo f WHERE f.parentId = :parentId AND f.isDeleted = false")
    List<FileInfo> findChildrenByParentId(@Param("parentId") Long parentId);

    /**
     * 统计系统总文件数
     */
    @Query("SELECT COUNT(f) FROM FileInfo f WHERE f.isDeleted = false AND f.isDirectory = false")
    long countTotalFiles();

    /**
     * 统计系统总存储使用量
     */
    @Query("SELECT SUM(f.fileSize) FROM FileInfo f WHERE f.isDeleted = false AND f.isDirectory = false")
    Long getTotalStorageUsed();

    // ==================== 文件夹管理相关查询方法 ====================

    /**
     * 查询指定父目录下的所有文件夹
     */
    @Query("SELECT f FROM FileInfo f WHERE f.ownerId = :ownerId AND f.parentId = :parentId AND f.isDirectory = true AND f.isDeleted = false ORDER BY f.originalName ASC")
    List<FileInfo> findFoldersByOwnerIdAndParentId(@Param("ownerId") Long ownerId, @Param("parentId") Long parentId);

    /**
     * 查询根目录下的所有文件夹
     */
    @Query("SELECT f FROM FileInfo f WHERE f.ownerId = :ownerId AND f.parentId IS NULL AND f.isDirectory = true AND f.isDeleted = false ORDER BY f.originalName ASC")
    List<FileInfo> findRootFoldersByOwnerId(@Param("ownerId") Long ownerId);

    /**
     * 统计指定文件夹下的文件数量（不包括子文件夹）
     */
    @Query("SELECT COUNT(f) FROM FileInfo f WHERE f.ownerId = :ownerId AND f.parentId = :folderId AND f.isDirectory = false AND f.isDeleted = false")
    Integer countFilesByFolderId(@Param("ownerId") Long ownerId, @Param("folderId") Long folderId);

    /**
     * 检查指定文件夹是否有子文件夹
     */
    @Query("SELECT COUNT(f) > 0 FROM FileInfo f WHERE f.ownerId = :ownerId AND f.parentId = :folderId AND f.isDirectory = true AND f.isDeleted = false")
    Boolean hasSubFolders(@Param("ownerId") Long ownerId, @Param("folderId") Long folderId);

    /**
     * 根据名称和父目录查找文件夹
     */
    @Query("SELECT f FROM FileInfo f WHERE f.ownerId = :ownerId AND f.originalName = :name AND f.parentId = :parentId AND f.isDeleted = false")
    Optional<FileInfo> findByOwnerIdAndNameAndParentId(@Param("ownerId") Long ownerId, @Param("name") String name, @Param("parentId") Long parentId);

    /**
     * 根据名称和父目录查找文件夹（父目录为null的情况）
     */
    @Query("SELECT f FROM FileInfo f WHERE f.ownerId = :ownerId AND f.originalName = :name AND f.parentId IS NULL AND f.isDeleted = false")
    Optional<FileInfo> findByOwnerIdAndNameAndParentIdIsNull(@Param("ownerId") Long ownerId, @Param("name") String name);

    // ==================== 搜索相关查询方法 ====================

    /**
     * 全局搜索文件（不限制用户）
     */
    @Query("SELECT f FROM FileInfo f WHERE f.isDeleted = false AND " +
           "(LOWER(f.originalName) LIKE LOWER(CONCAT('%', :keyword, '%')) OR LOWER(f.fileName) LIKE LOWER(CONCAT('%', :keyword, '%')))")
    Page<FileInfo> globalSearchFiles(@Param("keyword") String keyword, Pageable pageable);

    // ==================== 回收站相关查询方法 ====================

    /**
     * 根据用户ID查找回收站文件列表（分页）
     */
    Page<FileInfo> findByOwnerIdAndIsDeletedTrue(Long ownerId, Pageable pageable);

    /**
     * 根据用户ID查找回收站文件列表
     */
    List<FileInfo> findByOwnerIdAndIsDeletedTrue(Long ownerId);

    /**
     * 根据ID、用户ID查找已删除文件
     */
    Optional<FileInfo> findByIdAndOwnerIdAndIsDeletedTrue(Long id, Long ownerId);

    /**
     * 根据用户ID、父目录ID查找已删除的子文件
     */
    List<FileInfo> findByOwnerIdAndParentIdAndIsDeletedTrue(Long ownerId, Long parentId);



    /**
     * 查找超过指定时间的已删除文件（用于自动清理）
     */
    @Query("SELECT f FROM FileInfo f WHERE f.isDeleted = true AND f.deletedAt < :cutoffDate")
    List<FileInfo> findExpiredDeletedFiles(@Param("cutoffDate") java.time.LocalDateTime cutoffDate);

    /**
     * 根据父目录ID查找已删除的子文件（用于递归清理）
     */
    List<FileInfo> findByParentIdAndIsDeletedTrue(Long parentId);
}
